Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MIN

Syntax

Description of the illustration min.gif

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

`MIN` returns minimum value of `expr`. You can use it as an aggregate or analytic function.

See Also: "About SQL Expressions" for information on valid forms of `expr`, "Floating-Point Numbers" for information on binary-float comparison semantics, and "Aggregate Functions"

Aggregate Example

The following statement returns the earliest hire date in the `hr.employees` table:

    SELECT MIN(hire_date) "Earliest" FROM employees;

    Earliest
    ---------
    17-JUN-87

Analytic Example

The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.

    SELECT manager_id, last_name, hire_date, salary,
       MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
       RANGE UNBOUNDED PRECEDING) AS p_cmin
       FROM employees;

    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     P_CMIN
    ---------- ------------------------- --------- ---------- ----------
           100 Kochhar                   21-SEP-89      17000      17000
           100 De Haan                   13-JAN-93      17000      17000
           100 Raphaely                  07-DEC-94      11000      11000
           100 Kaufling                  01-MAY-95       7900       7900
           100 Hartstein                 17-FEB-96      13000       7900
           100 Weiss                     18-JUL-96       8000       7900
           100 Russell                   01-OCT-96      14000       7900
           100 Partners                  05-JAN-97      13500       7900
           100 Errazuriz                 10-MAR-97      12000       7900
    . . .